set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 200;
insert
    overwrite
    table jms_dm.dm_cusc_customer_abnormal_flow_summary_dt
    partition
    (dt)
select customer_id                                    --客户id
     , customer_code                                  --客户编码
     , customer_name                                  --客户名称
     , customer_network_code                          --客户所属网点code
     , customer_network_name                          --客户所属网点name
     , customer_fran_code                             --客户所属加盟商code
     , customer_fran_name                             --客户所属加盟商name
     , customer_agent_code                            --客户所属代理区code
     , customer_agent_name                            --客户所属代理区name
     , order_source_code                              --订单来源code
     , order_source_name                              --订单来源名称
     , end_virt_code                                  --目的网点(代理区)虚拟代理区
     , end_virt_name                                  --目的网点(代理区)虚拟代理区
     , no_taketimely_num                              --未及时揽收量
     , no_collect_num                                 --不准点交件票量
     , false_taking_num                               --虚假揽收量
     , no_outtimely_num                               --出港不及时量
     , no_signtimely_num                              --未及时签收量
     , complaint_num                                  --客诉量
     , sign_difftime_num                              --超24小时揽签量
     , problem_num                                    --问题件量
     , lose_problem_num                               --问题件遗失量
     , worn_problem_num                               --问题件破损量
     , pollute_problem_num                            --问题件被污染量
     , other_problem_num                              --问题件其他量
     , (no_taketimely_num + no_collect_num + false_taking_num + no_outtimely_num + no_signtimely_num + complaint_num +
        sign_difftime_num + problem_num) as abn_total --异常总量
     , dt                                as date_time
     , take_input_hour                                --揽收时长
     , take_input_hour_num                            --揽收时长单量
     , deliver_sign_hour                              --派签时长
     , dt
from (
         select customer_id                                     as customer_id           --客户id
              , customer_code                                   as customer_code         --客户编码
              , customer_name                                   as customer_name         --客户名称
              , customer_network_code                           as customer_network_code --客户所属网点code
              , customer_network_name                           as customer_network_name --客户所属网点name
              , customer_fran_code                              as customer_fran_code    --客户所属加盟商code
              , customer_fran_name                              as customer_fran_name    --客户所属加盟商name
              , customer_agent_code                             as customer_agent_code   --客户所属代理区code
              , customer_agent_name                             as customer_agent_name   --客户所属代理区name
              , order_source_code                               as order_source_code     --订单来源code
              , order_source_name                               as order_source_name     --订单来源名称
              , end_virt_code                                   as end_virt_code         --目的虚拟代理区
              , end_virt_name                                   as end_virt_name         --目的虚拟代理区
              , sum(no_taketimely_flag)                         as no_taketimely_num     --未及时揽收量
              , sum(no_collect_flag)                            as no_collect_num        --不准点交件票量
              , sum(false_taking_flag)                          as false_taking_num      --虚假揽收量
              , sum(no_outtimely_flag)                          as no_outtimely_num      --出港不及时量
              , sum(no_signtimely_flag)                         as no_signtimely_num     --未及时签收量
              , sum(complaint_flag)                             as complaint_num         --客诉量
              , sum(sign_difftime_flag)                         as sign_difftime_num     --超24小时揽签量
              , sum(problem_flag)                               as problem_num           --问题件量
              , sum(if(problem_flag = 1 and array_contains(proble_type_subject_code, '41'), 1,
                       0))                                      as lose_problem_num      --问题件遗失量
              , sum(if(problem_flag = 1 and array_contains(proble_type_subject_code, '27'), 1,
                       0))                                      as worn_problem_num      --问题件破损量
              , sum(if(problem_flag = 1 and array_contains(proble_type_subject_code, '35'), 1,
                       0))                                      as pollute_problem_num   --问题件被污染量
              , sum(if(problem_flag = 1 and !array_contains(proble_type_subject_code, '41') and
                       !array_contains(proble_type_subject_code, '27') and
                       !array_contains(proble_type_subject_code, '35'), 1,
                       0))                                      as other_problem_num     --问题件其他量
              , sum(nvl(diff_take_input_hour, 0))               as take_input_hour       --揽收时长
              , sum(if(diff_take_input_hour is not null, 1, 0)) as take_input_hour_num   --揽收时长单量
              , sum(nvl(deliver_aging_sign_difftime, 0))        as deliver_sign_hour     --派签时长
              , dt
         from jms_dm.dm_cusc_customer_abnormal_waybill_detail_dt
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', -6)
         group by customer_id
                , customer_code
                , customer_name
                , customer_network_code
                , customer_network_name
                , customer_fran_code
                , customer_fran_name
                , customer_agent_code
                , customer_agent_name
                , order_source_code
                , order_source_name
                , end_virt_code
                , end_virt_name
                , dt
     ) a
    distribute by dt
;